package ru.nsu.effel.persistence;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.transform.Transformers;

import ru.nsu.effel.actions.ConnectionBidPrintHelper;

public class ConnectionBidManager {
	private static ConnectionBidManager instance = null;
	
	public static ConnectionBidManager getInstance() {
		if (null == instance) {
			instance = new ConnectionBidManager();
		}
		return instance;
	}
	
	private ConnectionBidManager() {
		
	}
	
	public List<ConnectionBid> getFitterConnectionBids(int accountId) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		List list = session.createSQLQuery("select id, firstName, lastName, tariffId, address from " +
		"connectionbids inner join fitter_connectionbid on connection_bid_id = id " +
		"where fitter_id = " + accountId).setResultTransformer(Transformers.aliasToBean(ConnectionBid.class)).list();
		session.getTransaction().commit();
		session.close();
		return list;
	}
	
	public List<ConnectionBid> getManagerConnectionBids(int accountId) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		List list = session.createSQLQuery("select id, firstName, lastName, tariffId, address, bidStatus from " +
		"connectionbids inner join manager_connectionbid on connection_bid_id = id " +
		"where manager_id = " + accountId).setResultTransformer(Transformers.aliasToBean(ConnectionBid.class)).list();
		session.getTransaction().commit();
		session.close();
		return list;
	}
	
	public List<ConnectionBid> getAllConnectionBids() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		List<ConnectionBid> connectionBids = session.createCriteria(ConnectionBid.class).addOrder(Order.asc("id")).setCacheable(false).list();
		session.getTransaction().commit();
		session.close();
		return connectionBids;
	}
	
	public void addConnectionBid(ConnectionBid bid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		session.save(bid);
		session.getTransaction().commit();
		session.close();		
	}
	
	public List<ConnectionBidPrintHelper> getConnectionBidIdTariff() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		List list = session.createSQLQuery("select connectionbids.id, tariffs.tariffname from connectionbids " +
		" inner join tariffs on tariffs.id = connectionbids.tariffid where connectionbids.bidStatus = 0;").setResultTransformer(Transformers.aliasToBean(ConnectionBidPrintHelper.class)).list();
		session.getTransaction().commit();
		session.close();
		return list;
	}
	
	public ConnectionBid getConnectionBidById(int id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		ConnectionBid bid = (ConnectionBid)session.get(ConnectionBid.class, id);
		session.getTransaction().commit();
		session.close();
		return bid;
	}
	
	public void addConnectionBidtoManager(int managerId, int connectionBidId) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		session.createSQLQuery("insert into manager_connectionbid (manager_id, connection_bid_id) " +
				"values(" +managerId+ "," +connectionBidId+ ");").executeUpdate();
		session.getTransaction().commit();
		session.close();
	}
	
	public void addConnectionBidtoFitter(int fitterId, int connectionBidId) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		session.createSQLQuery("insert into fitter_connectionbid (fitter_id, connection_bid_id) " +
				"values(" +fitterId+ "," +connectionBidId+ ");").executeUpdate();
		session.getTransaction().commit();
		session.close();
	}
	
	public void updateConnectionBid(ConnectionBid bid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		session.update(bid);
		session.getTransaction().commit();
		session.close();
	}
	
	public void deleteConnectionBidsFromFitter(int fitterId, int ids[]) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		for(int i = 0; i < ids.length; i++) {
			session.createSQLQuery("delete from fitter_connectionbid where " +
					" fitter_id = " +fitterId+ " and connection_bid_id = " +ids[i]+ ";").executeUpdate();
		}
		session.getTransaction().commit();
		session.close();
	}
	
	public void deleteConnectionBidsByIds(int[] ids) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		for(int i =0; i < ids.length; i++) {
			session.delete(session.get(ConnectionBid.class, ids[i]));
		}
		session.getTransaction().commit();
		session.close();
	}
	
	public void deleteConnectionBidsFromManager(int managerId, int[] ids) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		for(int i = 0; i < ids.length; i++) {
			session.createSQLQuery("delete from manager_connectionbid where " +
					" manager_id = " +managerId+ " and connection_bid_id = " +ids[i]+ ";").executeUpdate();
		}
		session.getTransaction().commit();
		session.close();
	}
}
